/**
 *
 * @author Alessandro Polverini <alex@nibbles.it>
 */
package com.google.bitcoin.store;

import com.google.bitcoin.core.*;
import java.math.BigInteger;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Stores the block chain to database, in "clear" form, easy to debug and search
 * Not designed for constrained environments like mobile phones, unless you want
 * to remotely connect to the database
 */
public class JdbcDiskBlockStore implements BlockStore {

    private static final Logger log = LoggerFactory.getLogger(JdbcDiskBlockStore.class);
    private NetworkParameters params;
    private DataSource dataSource;
    private String driverClassName, jdbcUrl, dbUser, dbPassword;
    private Connection dbConnection;
    private int connectionTimeout = 10;
    //private int chainId;
    private String chainName;
    private StoredBlock chainHead;
    protected JdbcIdGenerator blockIdGen;
    protected JdbcIdGenerator transactionIdGen;
    protected JdbcIdGenerator txInputsIdGen;
    protected JdbcIdGenerator txOutputsIdGen;
    final private String blockSqlFields = "id, hash, hashMerkleRoot, nTime, nBits, nonce, height, prevBlockHash, chainWork";
    final private String sqlPutBlock = "INSERT INTO Block(" + blockSqlFields + ") VALUES(?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE nTime=nTime";
    private PreparedStatement psPutBlock;
    final private String sqlGetBlock = "SELECT " + blockSqlFields + " FROM Block WHERE hash=?";
    private PreparedStatement psGetBlock;
    //final private String sqlCreateChainHead = "INSERT INTO Chain(id,name,headHash) VALUES(?,?,?)";
    final private String sqlCreateChainHead = "INSERT INTO Chain(headHash) VALUES(?)";
    final private String sqlGetChainHead = "SELECT * FROM Chain";
    //final private String sqlUpdateChainHead = "UPDATE Chain SET headHash=? WHERE id=?";
    final private String sqlUpdateChainHead = "UPDATE Chain SET headHash=?";    // We have just one record, for now
    private PreparedStatement psUpdateChainHead;
    final private String sqlPutTransaction = "INSERT INTO Transaction(id,hash,lockTime) VALUES(?,?,?)";
    private PreparedStatement psPutTransaction;
    final private String sqlGetTransaction = "SELECT id,lockTime FROM Transaction WHERE hash=?";
    private PreparedStatement psGetTransaction;
    final private String sqlGetBlockTransactionsFromId = "SELECT BlockTx.txId,Transaction.hash,Transaction.lockTime FROM BlockTx LEFT JOIN Transaction ON (BlockTx.txId = Transaction.id) WHERE blkId=? ORDER BY BlockTx.pos";
    private PreparedStatement psGetBlockTransactionsFromId;
    final private String sqlGetBlockTransactionsFromHash = "SELECT BlockTx.txId,Transaction.hash,Transaction.lockTime FROM BlockTx LEFT JOIN Transaction ON (BlockTx.txId = Transaction.id) LEFT JOIN Block ON (BlockTx.blockId = Block.id) WHERE Block.hash=? ORDER BY BlockTx.pos";
    private PreparedStatement psGetBlockTransactionsFromHash;
    final private String sqlPutTxInput = "INSERT INTO TxInput(id,txId,referredTxHash,referredTxIndex,sequence,scriptBytes) VALUES(?,?,?,?,?,?)";
    private PreparedStatement psPutTxInput;
    final private String sqlGetTxInputs = "SELECT id,referredTxHash,referredTxIndex,sequence,scriptBytes FROM TxInput WHERE txId=? ORDER BY id";
    private PreparedStatement psGetTxInputs;
    final private String sqlPutTxOutput = "INSERT INTO TxOutput(id,txId,value,scriptBytes) VALUES(?,?,?,?)";
    private PreparedStatement psPutTxOutput;
    final private String sqlGetTxOutputs = "SELECT id,value,scriptBytes FROM TxOutput WHERE txId=? ORDER BY id";
    private PreparedStatement psGetTxOutputs;
    final private String sqlPutBlkTxLink = "INSERT INTO BlockTx(blockId, txId, pos) VALUES(?,?,?)";
    private PreparedStatement psPutBlkTxLink;
    //final private String sqlGetBlkTxLinks = "SELECT txId FROM BlockTx WHERE blockId=? ORDER BY pos";
    //private PreparedStatement psGetBlkTxLinks;

    public JdbcDiskBlockStore(NetworkParameters params, String chainName, String driver, String url, String user, String pw) throws BlockStoreException {
        this.params = params;
        this.chainName = chainName;
        this.driverClassName = driver;
        this.jdbcUrl = url;
        this.dbUser = user;
        this.dbPassword = pw;
        try {
            Class.forName(driverClassName);
        } catch (ClassNotFoundException e) {
            throw new BlockStoreException("JDBC driver class not found; " + driverClassName);
        }
        commonInit();
    }

    public JdbcDiskBlockStore(NetworkParameters params, String chainName, DataSource dataSource) throws BlockStoreException {
        this.params = params;
        this.chainName = chainName;
        this.dataSource = dataSource;
        commonInit();
    }

    private void chainInit() throws BlockStoreException, SQLException {
        /*
         * JdbcIdGenerator idGen; if (dataSource != null) { idGen = new
         * JdbcIdGenerator(dataSource); } else { idGen = new
         * JdbcIdGenerator(driverClassName, jdbcUrl, dbUser, dbPassword); }
         * idGen.setIdReserveSize(1).setIdName("Chain"); chainId = (int)
         * idGen.getNewId();
         */
        // Set up the genesis block. When we start out fresh, it is by definition the top of the chain.
        Block genesisBlock = params.genesisBlock;
        try {
            chainHead = new StoredBlock(genesisBlock, genesisBlock.getWork(), 0);
        } catch (VerificationException ex) {
            throw new BlockStoreException(ex);
        }
        put(chainHead);
        PreparedStatement ps = dbConnection.prepareStatement(sqlCreateChainHead);
        //ps.setInt(1, chainId);
        //ps.setString(2, chainName);
        ps.setBytes(1, chainHead.getHeader().getHash().getBytes());
        ps.executeUpdate();
        ps.close();
    }

    private void commonInit() throws BlockStoreException {
        // TODO: Create tables
        if (dataSource != null) {
            blockIdGen = new JdbcIdGenerator(dataSource);
            transactionIdGen = new JdbcIdGenerator(dataSource);
            txInputsIdGen = new JdbcIdGenerator(dataSource);
            txOutputsIdGen = new JdbcIdGenerator(dataSource);
        } else {
            blockIdGen = new JdbcIdGenerator(driverClassName, jdbcUrl, dbUser, dbPassword);
            transactionIdGen = new JdbcIdGenerator(driverClassName, jdbcUrl, dbUser, dbPassword);
            txInputsIdGen = new JdbcIdGenerator(driverClassName, jdbcUrl, dbUser, dbPassword);
            txOutputsIdGen = new JdbcIdGenerator(driverClassName, jdbcUrl, dbUser, dbPassword);
        }
        blockIdGen.setIdName("Block").setIdReserveSize(100);
        transactionIdGen.setIdName("Transaction").setIdReserveSize(100);
        txInputsIdGen.setIdName("TxInput").setIdReserveSize(100);
        txOutputsIdGen.setIdName("TxOutput").setIdReserveSize(100);
        refreshConnectionAndPs();
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = dbConnection.prepareStatement(sqlGetChainHead);
            //ps.setString(1, chainName);
            rs = ps.executeQuery();
            if (!rs.next()) {
                chainInit();
            } else {
                chainHead = get(new Sha256Hash(rs.getBytes("headHash")));
                log.info("Chain '" + chainName + "' already initialized -- head: " + chainHead);
            }
            dbConnection.commit();
        } catch (SQLException e) {
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
            }
            throw new BlockStoreException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
            }
        }
    }

    private void refreshConnectionAndPs() throws BlockStoreException {
        try {
            if (dbConnection != null) {
                try {
                    dbConnection.close();
                } catch (SQLException e) {
                }
            }
            if (dataSource != null) {
                dbConnection = dataSource.getConnection();
            } else {
                dbConnection = DriverManager.getConnection(jdbcUrl, dbUser, dbPassword);
            }
            // TODO: Check if best practice is to close() old preparedStatements
            psGetBlock = dbConnection.prepareStatement(sqlGetBlock);
            psPutBlock = dbConnection.prepareStatement(sqlPutBlock);
            psUpdateChainHead = dbConnection.prepareStatement(sqlUpdateChainHead);
            psPutTransaction = dbConnection.prepareStatement(sqlPutTransaction);
            psGetTransaction = dbConnection.prepareStatement(sqlGetTransaction);
            psPutTxInput = dbConnection.prepareStatement(sqlPutTxInput);
            psGetTxInputs = dbConnection.prepareStatement(sqlGetTxInputs);
            psPutTxOutput = dbConnection.prepareStatement(sqlPutTxOutput);
            psGetTxOutputs = dbConnection.prepareStatement(sqlGetTxOutputs);
            psPutBlkTxLink = dbConnection.prepareStatement(sqlPutBlkTxLink);
            //psGetBlkTxLinks = dbConnection.prepareStatement(sqlGetBlkTxLinks);
            psGetBlockTransactionsFromId = dbConnection.prepareStatement(sqlGetBlockTransactionsFromId);
            psGetBlockTransactionsFromHash = dbConnection.prepareStatement(sqlGetBlockTransactionsFromHash);
            dbConnection.setAutoCommit(false);
        } catch (SQLException e) {
            throw new BlockStoreException(e);
        }
    }

    private void checkConnection() throws BlockStoreException {
        try {
            if (!dbConnection.isValid(connectionTimeout)) {
                refreshConnectionAndPs();
            }
        } catch (SQLException e) {
            throw new BlockStoreException(e);
        }
    }

    private synchronized long storeBlock(StoredBlock storedBlock) throws SQLException, BlockStoreException {
        Block block = storedBlock.getHeader();
        long blockId = blockIdGen.getNewId();
        log.info("[storeBlock id= " + blockId + " " + block.getHashAsString() + "]");
        psPutBlock.setLong(1, blockId);
        psPutBlock.setBytes(2, block.getHash().getBytes());
        psPutBlock.setBytes(3, block.getMerkleRoot().getBytes());
        psPutBlock.setLong(4, block.getTimeSeconds());
        psPutBlock.setLong(5, block.getDifficultyTarget());
        psPutBlock.setLong(6, block.getNonce());
        psPutBlock.setInt(7, storedBlock.getHeight());
        psPutBlock.setBytes(8, block.getPrevBlockHash().getBytes());
        psPutBlock.setString(9, storedBlock.getChainWork().toString());
        psPutBlock.executeUpdate();
        return blockId;
    }

    private synchronized long storeTransaction(Transaction tx) throws BlockStoreException, SQLException {
        long txId = transactionIdGen.getNewId();
        log.debug("Transazione con nuovo id: " + txId + " :" + tx.getHashAsString() + " txins: " + tx.getInputs().size() + " txouts: " + tx.getOutputs().size());
        psPutTransaction.setLong(1, txId);
        psPutTransaction.setBytes(2, tx.getHash().getBytes());
        psPutTransaction.setLong(3, tx.getLockTime());
        int n = psPutTransaction.executeUpdate();
        //log.debug("Numero record inseriti: " + n);

        for (TransactionInput tinput : tx.getInputs()) {
            long id = txInputsIdGen.getNewId();
            TransactionOutPoint outPoint = tinput.getOutpoint();
            boolean isCoinBase = tinput.isCoinBase();
            if (isCoinBase) {
                log.info("[txid " + txId + " TXIN COINBASE n: " + tinput.getSequence() + " scriptSize: " + tinput.getScriptBytes().length);
            } else {
                log.info("[txid " + txId + " TXIN parent: " + tinput.getParentTransaction().getHashAsString() + " index: " + outPoint.getIndex() + " scriptSize: " + tinput.getScriptBytes().length);
            }
            psPutTxInput.setLong(1, id);
            psPutTxInput.setLong(2, txId);
            if (isCoinBase) {
                psPutTxInput.setNull(3, Types.VARCHAR);
            } else {
                psPutTxInput.setBytes(3, outPoint.getHash().getBytes()); // tinput.getParentTransaction().getHashAsString()); ?
            }
            psPutTxInput.setLong(4, isCoinBase ? 0 : outPoint.getIndex());
            psPutTxInput.setLong(5, tinput.getSequence());
            psPutTxInput.setBytes(6, tinput.getScriptBytes());
            psPutTxInput.executeUpdate();
        }
        for (TransactionOutput tout : tx.getOutputs()) {
            long id = txOutputsIdGen.getNewId();
            if (tx.isCoinBase()) {
                log.info("[txid " + txId + " TXOUT value: " + tout.getValue() + " script len: " + tout.getScriptBytes().length);
            } else {
                log.info("[txid " + txId + " TXOUT value: " + tout.getValue() + " script len: " + tout.getScriptBytes().length);
                //log.info("[txid "+txId+" txout: "+tout+" value: "+tout.getValue()+" script len: "+tout.getScriptBytes().length+" scriptPubKey: "+tout.getScriptPubKey());
            }
            psPutTxOutput.setLong(1, id);
            psPutTxOutput.setLong(2, txId);
            psPutTxOutput.setString(3, tout.getValue().toString());
            psPutTxOutput.setBytes(4, tout.getScriptBytes());
            psPutTxOutput.executeUpdate();
        }
        return txId;
    }

    private synchronized void storeBlkTxLink(long blockId, long txId, int pos) throws SQLException {
        psPutBlkTxLink.setLong(1, blockId);
        psPutBlkTxLink.setLong(2, txId);
        psPutBlkTxLink.setLong(3, pos);
        psPutBlkTxLink.executeUpdate();
    }

    // TODO: Decide if close the rs in the usual final clause and rethrow the possible SQLException
    private void loadTxInputsOutputs(Transaction tx, long txId) throws SQLException {
        ArrayList<TransactionInput> inputs = new ArrayList<TransactionInput>();
        psGetTxInputs.setLong(1, txId);
        ResultSet rs = psGetTxInputs.executeQuery();
        while (rs.next()) {
            Sha256Hash referredTxHash = new Sha256Hash(rs.getBytes("referredTxHash"));
            if (rs.wasNull())
                referredTxHash = Sha256Hash.ZERO_HASH;
            inputs.add(new TransactionInput(params, tx, referredTxHash, rs.getLong("referredTxIndex"), rs.getLong("sequence"), rs.getBytes("scriptBytes")));
        }
        rs.close();
        tx.setInputs(inputs);

        ArrayList<TransactionOutput> outputs = new ArrayList<TransactionOutput>();
        psGetTxOutputs.setLong(1, txId);
        rs = psGetTxOutputs.executeQuery();
        while (rs.next()) {
            outputs.add(new TransactionOutput(params, tx, rs.getLong("value"), rs.getBytes("scriptBytes")));
        }
        rs.close();
        tx.setOutputs(outputs);
    }

    // TODO: Decide if close the rs in the usual final clause and rethrow the possible SQLException
    private List<Transaction> getBlockTransactions(ResultSet rs) throws SQLException, ProtocolException {
        ArrayList<Transaction> res = new ArrayList<Transaction>();
        while (rs.next()) {
            long txId = rs.getLong("txId");
            Transaction tx = new Transaction(params, new Sha256Hash(rs.getBytes("hash")), rs.getLong("lockTime"));
            loadTxInputsOutputs(tx, txId);
            res.add(tx);
        }
        rs.close();
        return res;
    }

    public List<Transaction> getBlockTransactions(Sha256Hash hash) throws SQLException, ProtocolException {
        return getBlockTransactions(hash.toString());
    }

    public synchronized List<Transaction> getBlockTransactions(String hash) throws SQLException, ProtocolException {
        psGetBlockTransactionsFromHash.setString(1, hash);
        return getBlockTransactions(psGetBlockTransactionsFromHash.executeQuery());
    }

    // TODO: Decide if close the rs in the usual final clause and rethrow the possible SQLException
    public synchronized List<Transaction> getBlockTransactions(long blockId) throws SQLException, ProtocolException {
        psGetBlockTransactionsFromId.setLong(1, blockId);
        return getBlockTransactions(psGetBlockTransactionsFromId.executeQuery());
    }

    public synchronized long put(Transaction tx) throws BlockStoreException {
        try {
            checkConnection();
            long id = storeTransaction(tx);
            dbConnection.commit();
            return id;
        } catch (SQLException e) {
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
            }
            throw new BlockStoreException(e);
        }
    }

    /*
     * TODO: valutare questo codice postgresql: CREATE RULE
     * "my_table_on_duplicate_ignore" AS ON INSERT TO "my_table" WHERE
     * EXISTS(SELECT 1 FROM my_table WHERE (pk_col_1, pk_col_2)=(NEW.pk_col_1,
     * NEW.pk_col_2)) DO INSTEAD NOTHING; INSERT INTO my_table SELECT * FROM
     * another_schema.my_table WHERE some_cond; DROP RULE
     * "my_table_on_duplicate_ignore" ON "my_table";
     */
    public synchronized void put(StoredBlock storedBlock) throws BlockStoreException {
        //assert blockMap.get(hash) == null : "Attempt to insert duplicate";
        try {
            checkConnection();
            long blockId = storeBlock(storedBlock);

            List<Transaction> transactions = storedBlock.getHeader().getTransactions();
            int pos = 0;
            for (Transaction tx : transactions) {
                long txId = getTransactionId(tx.getHash());
                if (txId == -1) {
                    txId = storeTransaction(tx);
                }
                storeBlkTxLink(blockId, txId, pos++);
            }

            dbConnection.commit();
        } catch (SQLException e) {
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
            }
            throw new BlockStoreException(e);
        }
    }

    //private long getTransactionId(String hash) throws SQLException {
    private long getTransactionId(Sha256Hash hash) throws SQLException {
        long txId = -1;
        synchronized (sqlGetTransaction) {
            psGetTransaction.setBytes(1, hash.getBytes());
            ResultSet rs = psGetTransaction.executeQuery();
            if (rs.next()) {
                txId = rs.getLong("id");
            }
            rs.close();
            return txId;
        }
    }

    public Transaction getTransaction(String hash) throws BlockStoreException {
        return getTransaction(new Sha256Hash(hash), true);
    }

    public Transaction getTransaction(Sha256Hash hash) throws BlockStoreException {
        return getTransaction(hash, true);
    }

    public Transaction getTransaction(Sha256Hash hash, boolean complete) throws BlockStoreException {
        ResultSet rs = null;
        long txId;
        synchronized (sqlGetTransaction) {
            try {
                checkConnection();
                psGetTransaction.setBytes(1, hash.getBytes());
                rs = psGetTransaction.executeQuery();
                Transaction tx;
                if (rs.next()) {
                    tx = new Transaction(params, hash, rs.getLong("lockTime"));
                    txId = rs.getLong("id");
                } else {
                    return null;
                }
                if (complete) {
                    loadTxInputsOutputs(tx, txId);
                }
                return tx;
            } catch (ProtocolException e) {
                throw new BlockStoreException(e);
            } catch (SQLException e) {
                throw new BlockStoreException(e);
            } finally {
                if (rs != null) {
                    try {
                        rs.close();
                    } catch (SQLException ex) {
                    }
                }
            }
        }
    }

    public synchronized StoredBlock get(String hash) throws BlockStoreException {
        return get(new Sha256Hash(hash));
    }

    public synchronized StoredBlock get(Sha256Hash hash) throws BlockStoreException {
        log.info("[getBlock " + hash + " ]");
        ResultSet rs = null;
        try {
            checkConnection();
            psGetBlock.setBytes(1, hash.getBytes());
            rs = psGetBlock.executeQuery();
            if (rs.next()) {
                Block block = new Block(params, 1, new Sha256Hash(rs.getBytes("prevBlockHash")), new Sha256Hash(rs.getBytes("hashMerkleRoot")),
                        rs.getLong("nTime"), rs.getLong("nBits"), rs.getLong("nonce"), new Sha256Hash(rs.getBytes("hash")));
                return new StoredBlock(block, new BigInteger(rs.getString("chainWork")), rs.getInt("height"));
            } else {
                return null;
            }
        } catch (SQLException e) {
            throw new BlockStoreException(e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    protected Map<Sha256Hash, SimplifiedStoredBlock> getBlocksAtHeight(int height) throws BlockStoreException {
        Map<Sha256Hash, SimplifiedStoredBlock> blocks = new HashMap<Sha256Hash, SimplifiedStoredBlock>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = dbConnection.prepareStatement("SELECT * FROM Block WHERE height=?");
            ps.setInt(1, height);
            rs = ps.executeQuery();
            while (rs.next()) {
                SimplifiedStoredBlock ssb = new SimplifiedStoredBlock(rs);
                blocks.put(ssb.hash, ssb);
            }
        } catch (SQLException e) {
            throw new BlockStoreException("SQLEx while checking block chain membership: " + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
            }
        }
        return blocks;
    }

    protected SimplifiedStoredBlock getBlockWithHigherWork(int height, BigInteger chainWork) throws BlockStoreException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = dbConnection.prepareStatement("SELECT * FROM Block WHERE height<? AND chainWork>? ORDER BY chainWork DESC LIMIT 1");
            ps.setInt(1, height);
            ps.setString(2, chainWork.toString());
            rs = ps.executeQuery();
            if (rs.next()) {
                return new SimplifiedStoredBlock(rs);
            }
        } catch (SQLException e) {
            throw new BlockStoreException("SQLEx while checking block chain membership: " + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
            }
        }
        return null;
    }

    /**
     * Returns true if the block is the chain with more work
     *
     * @param inputStoredBlock
     * @return
     * @throws BlockStoreException
     */
    public boolean isBlockInMainChain(SimplifiedStoredBlock inputStoredBlock) throws BlockStoreException {
        //SimplifiedStoredBlock myChain = new SimplifiedStoredBlock(inputStoredBlock);
        SimplifiedStoredBlock myChain = inputStoredBlock;

        // As first thing let's build a list of eventual side chains
        int currHeight = myChain.height;
        Map<Sha256Hash, SimplifiedStoredBlock> chains = getBlocksAtHeight(currHeight);

        // Remove our chain from this list
        for (SimplifiedStoredBlock b : chains.values()) {
            if (b.equals(myChain)) {
                chains.remove(b.hash);
            }
        }

        // Let's check if there is a shorter (height-1) orphan chain but with more work
        // If there are more chains we get just the higher one b/c it's the only one candidate for being the main chain
        SimplifiedStoredBlock orphanedChain = getBlockWithHigherWork(currHeight, inputStoredBlock.chainWork);
        // We have to check that it's really an orphan, i.e. it has no blocks over it
        if (orphanedChain != null) {
            for (SimplifiedStoredBlock b : chains.values()) {
                if (b.prevBlockHash.equals(orphanedChain.hash)) {
                    orphanedChain = null;
                    break;
                }
            }
            if (orphanedChain != null) {
                chains.put(orphanedChain.hash, orphanedChain);
            }
        }


        // Follow all the chains we discover until we are certain whether we are in main or side chain
        // or until we reach top of the chain
        do {
            currHeight++;
            Map<Sha256Hash, SimplifiedStoredBlock> blocks = getBlocksAtHeight(currHeight);

            // If no blocks are returned then all chains have been explored, we now need
            // to check if we are the only chain left or if other chains have more work in them
            if (blocks.isEmpty()) {
                for (SimplifiedStoredBlock b : chains.values()) {
                    if (b.chainWork.compareTo(myChain.chainWork) > 0) {
                        return false;
                    }
                }
                return true;
            }

            // Follow all chains included ours
            for (SimplifiedStoredBlock b : blocks.values()) {
                if (b.prevBlockHash.equals(myChain.hash)) {
                    myChain = b;
                } else {
                    chains.remove(b.prevBlockHash);
                }
            }

            // Eliminate our chain from the others we are checking against
            blocks.remove(myChain.hash);

            // We also need to carry around orphaned chains (chains with height < currentHeight) if they have higher chain work
            for (SimplifiedStoredBlock b : chains.values()) {
                if (b.chainWork.compareTo(myChain.chainWork) > 0) {
                    blocks.put(b.hash, b);
                }
            }

            // If our height is less than current height than we are inside an orphaned chain
            // in such a situation we have to check if longer chains have more work in them
            if (myChain.height < currHeight) {
                for (SimplifiedStoredBlock b : blocks.values()) {
                    if (b.chainWork.compareTo(myChain.chainWork) > 0) {
                        return false;
                    }
                }
            }

            // blocks became our current list of chains and iterate if there are more chains to follow
            chains = blocks;
        } while (!chains.isEmpty());

        // No other chains with higher work, so we are in the main one
        return true;
    }

    public synchronized boolean isTxInMainChain(Sha256Hash hash) throws BlockStoreException {
        String sql = "SELECT Block.* FROM Block LEFT JOIN BlockTx ON (Block.id = BlockTx.blockId)" +
                " LEFT JOIN Transaction ON (Transaction.id = BlockTx.txId) WHERE Transaction.hash=?";
        ArrayList<SimplifiedStoredBlock> blocks = new ArrayList<SimplifiedStoredBlock>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = dbConnection.prepareStatement(sql);
            ps.setBytes(1, hash.getBytes());
            rs = ps.executeQuery();
            while (rs.next()) {
                SimplifiedStoredBlock ssb = new SimplifiedStoredBlock(rs);
                if (isBlockInMainChain(ssb)) {
                    return true;
                }
            }
            return false;
        } catch (SQLException e) {
            throw new BlockStoreException("SQLEx while checking block chain membership: " + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
            }
        }
    }

    public synchronized boolean isTxInputRedeemedInMainChain(Sha256Hash hash, long index) throws BlockStoreException {
        String sql = "SELECT Block.* FROM Block LEFT JOIN BlockTx ON (Block.id = BlockTx.blockId)" +
                " LEFT JOIN Transaction ON (Transaction.id = BlockTx.txId) LEFT JOIN TxInput ON (Transaction.id = TxInput.txId)" +
                " WHERE referredTxHash=? AND referredTxIndex=?";
        ArrayList<SimplifiedStoredBlock> blocks = new ArrayList<SimplifiedStoredBlock>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = dbConnection.prepareStatement(sql);
            ps.setBytes(1, hash.getBytes());
            ps.setLong(2, index);
            rs = ps.executeQuery();
            while (rs.next()) {
                SimplifiedStoredBlock ssb = new SimplifiedStoredBlock(rs);
                if (isBlockInMainChain(ssb)) {
                    return true;
                }
            }
            return false;
        } catch (SQLException e) {
            throw new BlockStoreException("SQLEx while checking block chain membership: " + e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException ex) {
            }
        }
    }

    public synchronized StoredBlock getChainHead() throws BlockStoreException {
        return chainHead;
    }

    public synchronized void setChainHead(StoredBlock newChainHead) throws BlockStoreException {
        try {
            checkConnection();
            psUpdateChainHead.setBytes(1, newChainHead.getHeader().getHash().getBytes());
            //psUpdateChainHead.setInt(2, chainId);
            psUpdateChainHead.executeUpdate();
            dbConnection.commit();
            chainHead = newChainHead;
        } catch (SQLException e) {
            try {
                dbConnection.rollback();
            } catch (SQLException ex) {
            }
            throw new BlockStoreException(e);
        }
    }

    protected class SimplifiedStoredBlock { //implements Comparable<SimplifiedStoredBlock> {

        public Sha256Hash prevBlockHash;
        public Sha256Hash hash;
        public BigInteger chainWork;
        public int height;

        public SimplifiedStoredBlock(StoredBlock b) throws SQLException {
            prevBlockHash = b.getHeader().getPrevBlockHash();
            hash = b.getHeader().getHash();
            chainWork = b.getChainWork();
            height = b.getHeight();
        }

        public SimplifiedStoredBlock(ResultSet rs) throws SQLException {
            prevBlockHash = new Sha256Hash(rs.getBytes("prevBlockHash"));
            hash = new Sha256Hash(rs.getBytes("hash"));
            chainWork = new BigInteger(rs.getString("chainWork"));
            height = rs.getInt("height");
        }

        public int compareTo(SimplifiedStoredBlock o) {
            return o.hash.toString().compareTo(hash.toString());
        }

        @Override
        public boolean equals(Object obj) {
            if (!(obj instanceof SimplifiedStoredBlock)) {
                return false;
            }
            return hash.equals(((SimplifiedStoredBlock) obj).hash);
        }
    }
}
